SQL Server
Querying Overview
Azure SQL and SQL Server use the SQL query language to retrieve data. The general format of queries is
SELECT
FROM
WHERE
ORDER BY
The relevant SQL references can be found at
https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver16
Handling JSON Data
SQL Server supports storing JSON in string form and provides several functions for interacting with JSON string values. A simple table with a JSON column can be created by running
CREATE TABLE JSON_Products (
ProductID INT PRIMARY KEY,
ProductInfo NVARCHAR(MAX)
);
A couple of rows can be inserted using
INSERT INTO JSON_Products (ProductID, ProductInfo) VALUES
(1, '{"name": "Widget", "price": 19.99, "stock": 100, "categories": ["electronics", "gadgets"]}'),
(2, '{"name": "Gizmo", "price": 24.99, "stock": 50, "categories": ["electronics", "toys"]}');
This can be queried using
select * from JSON_Products
A sample row is shown below.
Notice the ProductInfo result is a simple string.
We can coerce the string into a real JSON object using this query specification.
#pragma convertToObject ProductInfo
select * from JSON_Products
A sample row in this case is shown below.
Notice the ProductInfo is now a real JSON object.
See the details on using Qarbine pragmas in the Data Source Designer guide.
Here is an example of using the JSON_VALUE function to extract specific field values.
SELECT ProductID, JSON_VALUE(ProductInfo, '$.name') AS ProductName,
JSON_VALUE(ProductInfo, '$.price') AS Price,
JSON_VALUE(ProductInfo, '$.stock') AS Stock
FROM JSON_Products ;
A sample row is shown below.
Here is an example of using the JSON_QUERY function.
SELECT ProductID, JSON_VALUE(ProductInfo, '$.name') AS ProductName,
JSON_QUERY(ProductInfo, '$.categories') AS Categories
FROM JSON_Products ;
A sample row is shown below.
Here is an example of using OPENJSON to expand (unnest) JSON array elements.
SELECT
p.ProductID,
JSON_VALUE(p.ProductInfo, '$.name') AS ProductName,
c.value AS Category
FROM JSON_Products p
CROSS APPLY OPENJSON(JSON_QUERY(p.ProductInfo, '$.categories')) c;
A sample row is shown below.
Notice that this explodes the answer set. A result row is created for each element in the categories array for each base row. This unnesting is sometimes required by legacy tools, but it results in a large burden for the tools or the people having to deal with the resulting answer set. In contrast, Qarbine is fine without unnesting the JSON object and avoids the answer set explosion in size.
Handling Boolean Data Type
SQL Server supports the BIT data type to represent boolean values. Here is an example table creation statement which uses this data type.
CREATE TABLE UserPreferences (
UserID INT PRIMARY KEY,
Username NVARCHAR(50) NOT NULL,
ReceiveEmails BIT NOT NULL DEFAULT 0,
DarkModeEnabled BIT NOT NULL DEFAULT 0,
IsActive BIT NOT NULL DEFAULT 1
);
Statements to insert sample data are shown below.
INSERT INTO
UserPreferences (UserID, Username, ReceiveEmails, DarkModeEnabled, IsActive)
VALUES
(1, 'john_doe', 1, 0, 1),
(2, 'jane_smith', 0, 1, 1),
(3, 'bob_johnson', 1, 1, 0),
(4, 'alice_brown', 0, 0, 1);
A sample query is shown below.
SELECT *
FROM UserPreferences
WHERE IsActive = 1;
A sample result is shown below.
Notice that the query used a value of 1 to represent true while the answer set has explicit boolean values.
Answer Set Considerations
Using Pragmas for JSON Data
Below are a few of the pragmas from that documentation to consider using with the Oracle JSON data. The full set is in the Data Source Designer guide.
| Pragma Keyword | Description |
|---|---|
| convertToObject | Provide a CSV list of fields to convert strings to JSON objects via JSON.parse(someString). This is done in-place. |
| deleteFields | Provide a CSV list of fields to delete. The arguments may have field paths of up to 2 levels. The first level can be a document or an array of documents. The deleting is done in-place. This is convenient when the result row/document has many fields and you want all just a few of them. Rather than explicitly list the 20 say fields of the 23, just ‘delete’ those 3 from the answer set. |
| pullFieldsUp | Provide a CSV list of object fields to pull their contents up to the first level. The original field is deleted. This can be useful when there are many inner fields that can be part of the first level. Instead of several template formulas like #container.first and #container.last you can simply use #first and #last via the line #pragma pullFieldsUp container |
Date and Timestamp Handling
You can query date and timestamp fields using various date functions , operators, and formats to perform filtering, manipulation, and formatting. Here's a quick guide to common query syntax and options based on a table called Orders with a column OrderDate of type DATETIME.
- Basic query for date and time fields
SELECT * FROM Orders
WHERE OrderDate = '2025-03-24'; -- Exact match for date
SQL Server interprets the string '2025-03-24' as a date.
2. Query for a Date Range using operators like BETWEEN or >= and <=.
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2025-03-01' AND '2025-03-24';
This retrieves all records with OrderDate between March 1 and March 24, 2025.
3. Filter by date parts (Year, Month, Day, etc.) using the date functions such as YEAR(), MONTH(), or DAY().
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) = 3;
This retrieves all orders placed in March 2025.
4. Extract specific date pParts using functions to retrieve specific parts of a DATETIME value, such as the year or hour.
SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month,
DAY(OrderDate) AS Day
FROM Orders;
- Format date values using the FORMAT() function to display a date in a specific format.
SELECT FORMAT(OrderDate, 'yyyy-MM-dd') AS FormattedDate
FROM Orders;
This displays OrderDate in the format YYYY-MM-DD.
6. Query for time portion by filtering by the time portion of a DATETIME value using the CONVERT() function.
SELECT * FROM Orders
WHERE CONVERT(VARCHAR(8), OrderDate, 108) = '12:00:00'; -- 12:00:00 PM
- Use GETDATE() for current date/time filterinf records based on the current date and time using GETDATE().
SELECT * FROM Orders
WHERE OrderDate >= GETDATE(); -- Future orders
- SQL Server offers several functions for working with dates and timestamps:
- DATEADD: Add or subtract intervals (e.g., days, months).
- DATEDIFF: Calculate the difference between two dates.
- ISDATE: Check if a value is a valid date.
- SYSDATETIME: Get the system's current date and time.
An example of adding days is
SELECT DATEADD(DAY, 7, GETDATE()) AS OneWeekLater;
Vector Searches
An overview of vector searching can be found at
https://learn.microsoft.com/en-us/sql/relational-databases/vectors/vectors-sql-server?view=azuresqldb-current
Vector search is in preview
https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type?view=azuresqldb-current&tabs=csharp-sample
Schema Context
Qarbine uses the Node.js mssql package to connect to SQL Server. In this usage schemas are treated as part of the table's fully qualified name. For example,
SELECT * FROM SalesLT.Products
Unfortunately, you can't directly set a default schema for all queries within the Node.js mssql package, as SQL Server defaults to using the schema associated with the user in its database settings. However, as shown above you can write queries to explicitly target specific schemas.
An alternative is to adjust SQL Server permissions for the database user associated with the Qarbine data service. For example:
ALTER USER your_username WITH DEFAULT_SCHEMA = SalesLT;
Troubleshooting
There are a variety of SQL Server tools ranging from Microsoft’s own tools to third party ones. Be sure that when comparing results you maintain consistent user sign on so that any table and other permissions are consistent.